*Written by David Zhang, unknown date
*Edited by Jason Premo on 7/15/16
** This file is called by psid_clean, and should not be called on its own.

*Filepath: /shared/sdsjxs/Townsend/scripts/psid_consumption.do

clear all
set more off
set maxvar 11000

cd "/shared/sdsjxs/Townsend/rawdata/psid"


#delimit ;

**************************************************************************
*   Label           : 2013 Consumption File
*   Rows            : 9063
*   Columns         : 43
*   ASCII File Date : October 15, 2015
*************************************************************************;

infix 
      REL13           1 - 1         CON13_ID        2 - 6         FOOD13          7 - 16   
      FDHM13         17 - 26        FDOUT13        27 - 36        FDDEL13        37 - 46   
      HOUS13         47 - 56        MORT13         57 - 66        RENT13         67 - 76   
      PRPTAX13       77 - 86        HMEINS13       87 - 96        UTIL13         97 - 106  
      HEAT13        107 - 116       ELECTR13      117 - 126       WATER13       127 - 136  
      OUTIL13       137 - 146       TELINT13      147 - 156       TRAN13        157 - 166  
      VEHLN13       167 - 176       VEHPAY13      177 - 186       VEHLS13       187 - 196  
      AUTOIN13      197 - 206       VEHADD13      207 - 216       VEHREP13      217 - 226  
      GAS13         227 - 236       PARK13        237 - 246       BUS13         247 - 256  
      CAB13         257 - 266       OTRAN13       267 - 276       ED13          277 - 286  
      CHILD13       287 - 296       HEALTH13      297 - 306       HOS13         307 - 316  
      DOCTOR13      317 - 326       PRESCR13      327 - 336       HINS13        337 - 346  
      HHREP13       347 - 356       FURN13        357 - 366       CLOTH13       367 - 376  
      TRIPS13       377 - 386       OTHREC13      387 - 396  
using CON13.txt, clear 
;
label variable  REL13      "2013 CONSUMPTION RELEASE NUMBER" ;                 
label variable  CON13_ID   "2013 FAMILY INTERVIEW (ID) NUMBER" ;               
label variable  FOOD13     "2013 FOOD EXPENDITURE" ;                           
label variable  FDHM13     "2013 FOOD AT HOME EXPENDITURE" ;                   
label variable  FDOUT13    "2013 FOOD AWAY FROM HOME EXPENDITURE" ;            
label variable  FDDEL13    "2013 FOOD DELIVERED EXPENDITURE" ;                 
label variable  HOUS13     "2013 HOUSING EXPENDITURE" ;                        
label variable  MORT13     "2013 MORTGAGE EXPENDITURE" ;                       
label variable  RENT13     "2013 RENT EXPENDITURE" ;                           
label variable  PRPTAX13   "2013 PROPERTY TAX EXPENDITURE" ;                   
label variable  HMEINS13   "2013 HOME INSURANCE EXPENDITURE" ;                 
label variable  UTIL13     "2013 UTILITY EXPENDITURE" ;                        
label variable  HEAT13     "2013 GAS FOR HOME EXPENDITURE" ;                   
label variable  ELECTR13   "2013 ELECTRICITY EXPENDITURE" ;                    
label variable  WATER13    "2013 WATER/SEWER EXPENDITURE" ;                    
label variable  OUTIL13    "2013 OTHER UTILITY EXPENDITURE" ;                  
label variable  TELINT13   "2013 TELEPHONE/INTERNET EXPENDITURE" ;             
label variable  TRAN13     "2013 TRANSPORTATION EXPENDITURE" ;                 
label variable  VEHLN13    "2013 VEHICLE LOAN PAYMENT EXPENDITURE" ;           
label variable  VEHPAY13   "2013 VEHICLE DOWN PAYMENT EXPENDITURE" ;           
label variable  VEHLS13    "2013 VEHICLE LEASE PAYMENT EXPENDITURE" ;          
label variable  AUTOIN13   "2013 AUTO INSURANCE EXPENDITURE" ;                 
label variable  VEHADD13   "2013 ADDITIONAL VEHICLE EXPENDITURE" ;             
label variable  VEHREP13   "2013 VEHICLE REPAIR EXPENDITURE" ;                 
label variable  GAS13      "2013 GASOLINE EXPENDITURE" ;                       
label variable  PARK13     "2013 PARKING EXPENDITURE" ;                        
label variable  BUS13      "2013 BUS/TRAIN EXPENDITURE" ;                      
label variable  CAB13      "2013 TAXICAB EXPENDITURE" ;                        
label variable  OTRAN13    "2013 OTHER TRANSPORTATION EXPENDITURE" ;           
label variable  ED13       "2013 EDUCATION EXPENDITURE" ;                      
label variable  CHILD13    "2013 CHILDCARE EXPENDITURE" ;                      
label variable  HEALTH13   "2013 HEALTH CARE EXPENDITURE" ;                    
label variable  HOS13      "2013 HOSPITAL/NURSING HOME EXPENDITURE" ;          
label variable  DOCTOR13   "2013 DOCTOR EXPENDITURE" ;                         
label variable  PRESCR13   "2013 PRESCRIPTIONS/OTHER EXPENDITURE" ;            
label variable  HINS13     "2013 HEALTH INSURANCE EXPENDITURE" ;               
label variable  HHREP13    "2013 HOUSEHOLD REPAIRS EXPENDITURE" ;              
label variable  FURN13     "2013 HOUSEHOLD FURNISHING EXPENDITURE" ;           
label variable  CLOTH13    "2013 CLOTHING EXPENDITURE" ;                       
label variable  TRIPS13    "2013 TRIPS EXPENDITURE" ;                          
label variable  OTHREC13   "2013 OTHER RECREATION EXPENDITURE" ;               

global convars "FOOD13   FDHM13  FDOUT13   FDDEL13   HOUS13   MORT13  RENT13 PRPTAX13 HMEINS13 UTIL13 HEAT13 ELECTR13 WATER13 OUTIL13 
TELINT13 TRAN13 VEHLN13 VEHPAY13 VEHLS13 AUTOIN13  VEHADD13 VEHREP13 GAS13 PARK13 BUS13 CAB13 OTRAN13 ED13 CHILD13 HEALTH13 HOS13 
DOCTOR13 PRESCR13 HINS13  HHREP13 FURN13 CLOTH13 TRIPS13 OTHREC13";               

#delimit cr


rename CON13_ID ER53002
merge 1:1 ER53002 using J196012/psid2013.dta
drop _merge

gen weight = ER58257

*Some tests to ensure I avoid double-counting /*
egen double foodtest = rowtotal(FDHM13 FDOUT13 FDDEL13)
assert FOOD13 == foodtest
egen double housetest = rowtotal(MORT13 RENT13 PRPTAX13 HMEINS13 HEAT13 ELECTR13 WATER13 OUTIL13 TELINT13)
assert HOUS13 == housetest
egen double trantest = rowtotal(VEHLN13 VEHPAY13 VEHLS13 AUTOIN13 VEHADD13 VEHREP13 GAS13 PARK13 BUS13 CAB13 OTRAN13)
assert TRAN13 == trantest
egen double healthtest = rowtotal(HOS13 DOCTOR13 PRESCR13 HINS13)
assert HEALTH13 == healthtest

*Not sure about these ... in housing? HHREP13 FURN13          
*/
/* These are family unit level, which might be compariable to the CEX's "consumer units", but I have no idea since it's quite vague - Mi: you might want to ask the PSID folks about this */


/* Convert to HH for Diary comparison paper since I know how many households there are in the US */
/* HH weight = (1/Number in HH)*\sum_{all families in HH} family weight * number of members in family, based on codebook for how they computed family weights (by averaging member weights) */

gen family_weight = weight*ER53016
collapse (sum) family_weight ER53016 $convars, by(ER58245)
gen aw = family_weight/ER53016

rename FOOD13 food
rename CLOTH13 clothing

replace HOUS13 = HOUS13 - TELINT13
rename HOUS13 housing
rename HEALTH13 health
rename TRAN13 transportation 

gen entertain = TRIPS13 + OTHREC13
rename PRPTAX13 property_tax
gen communication = TELINT13
rename ED13 education

keep aw food clothing housing health transportation entertain property_tax communication education

qui describe, varlist
egen double all = rowtotal(`r(varlist)')
replace all = all - aw
	
qui describe, varlist
local meanlist = ""
local medianlist = ""

*Not really using the medians, except for the totals
foreach variable in `r(varlist)' {
	if "`variable'" == "aw" {
		continue
		}
	local tempname1 = "mean_`variable' = `variable' "
	local tempname2 = "med_`variable' = `variable' "
	local meanlist = "`meanlist'"+"`tempname1'"
	local medianlist = "`medianlist'"+"`tempname2'"
	}

preserve

collapse (mean) `meanlist' (p50) med_all=all [aw = aw]
xpose, varname clear
rename _varname varname
order varname
replace v1 = round(v1)

export excel using "/shared/sdsjxs/Townsend/results/balance_sheet.xlsx", sheet("psid_expenditure", replace)
